# 基础的数据库读写文件，指将SQL语句提交给数据库的函数文件
from PIL.ImageFont import truetype
from sqlalchemy.sql import text
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from index.models import *
from datetime import datetime
from index.data_format_transformation import *
# from index.client_get_or_input_data import get_or_input_data


# 新增inverter_second_data元素参数数据
def write_inverter_second_data(db, data):
    """
    写入逆变器秒级数据
    :param db:
    :param data:
    :return:
    """
    try:
        sql = text("""  
                INSERT INTO inverter_second_data (  
                    save_time,   
                    inverter_active_power,   
                    inverter_output_current,   
                    inverter_output_voltage,   
                    inverter_output_freq,   
                    pv_active_power,   
                    pv_output_current,   
                    pv_output_voltage,   
                    storage_active_power,   
                    storage_output_current,   
                    storage_output_voltage,   
                    storage_soc,   
                    information  
                ) VALUES (  
                    :save_time,   
                    :inverter_active_power,   
                    :inverter_output_current,   
                    :inverter_output_voltage,   
                    :inverter_output_freq,   
                    :pv_active_power,   
                    :pv_output_current,   
                    :pv_output_voltage,   
                    :storage_active_power,   
                    :storage_output_current,   
                    :storage_output_voltage,   
                    :storage_soc,   
                    :information  
                )  
            """)

        # 使用 Session 执行 SQL
        with Session(db) as session:
            session.execute(sql, {
                "save_time": data.save_time,
                "inverter_active_power": data.inverter_active_power,
                "inverter_output_current": data.inverter_output_current,
                "inverter_output_voltage": data.inverter_output_voltage,
                "inverter_output_freq": data.inverter_output_freq,
                "pv_active_power": data.pv_active_power,
                "pv_output_current": data.pv_output_current,
                "pv_output_voltage": data.pv_output_voltage,
                "storage_active_power": data.storage_active_power,
                "storage_output_current": data.storage_output_current,
                "storage_output_voltage": data.storage_output_voltage,
                "storage_soc": data.storage_soc,
                "information": data.information
            })
            session.commit()  # 提交事务
    except Exception as e:
        print("在执行写入逆变器秒级数据遇到问题: " + str(e))
    return True


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# data = InverterSecondDataElement(save_time=datetime.now(),         # utcnow()返回全球统一时间，now()返回本时区时间
#         inverter_active_power=2234.56,
#         inverter_output_current=22.34,
#         inverter_output_voltage=230.5,
#         inverter_output_freq=60.0,
#         pv_active_power=667.89,
#         pv_output_current=6.67,
#         pv_output_voltage=120.5,
#         storage_active_power=889.01,
#         storage_output_current=8.89,
#         storage_output_voltage=58.5,
#         storage_soc=90.5,
#         information='示例数据2')
# write_inverter_second_data(db, data)

# 判断inverter_second_data是否有此时间段的数据
def exist_inverter_second_data(db, start_time=None, end_time=None):
    # SQL 查询语句
    sql = text("""
           SELECT 1
           FROM inverter_second_data
           WHERE save_time BETWEEN :start_time AND :end_time  
       """)
    # 使用 session 执行查询
    with Session(db) as session:
        result = session.execute(sql, {"start_time": start_time, "end_time": end_time})
    # 判断是否有数据
    if result.fetchone():  # `fetchone` 返回第一行数据，如果有数据就为真，否则为 None
        return True
    return False


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# start_time =  datetime(2024, 12, 2, 17, 4, 00)
# end_time =  datetime(2024, 12, 4, 17, 5, 00)
# print(exist_inverter_second_data(db, start_time, end_time))
# 因为数据库时间戳的秒太精准，只能这样按时间段查询

# 读取inverter_second_data在某时间段的数据
def read_inverter_second_data(db, start_time=None, end_time=None):
    # SQL 查询语句
    sql = text("""
           SELECT *
           FROM inverter_second_data
           WHERE save_time BETWEEN :start_time AND :end_time  
       """)
    # 使用 session 执行查询
    with Session(db) as session:
        result = session.execute(sql, {"start_time": start_time, "end_time": end_time})
    # 判断是否有数据
    rows = result.fetchall()
    return rows


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# start_time =  datetime(2024, 12, 4, 17, 5, 00)
# end_time =  datetime(2024, 12, 4, 17, 5, 00)
# print(read_inverter_second_data(db, start_time, end_time))

# 删除inverter_second_data在某时间段的数据
def delete_inverter_second_data(db, start_time=None, end_time=None):
    # SQL 查询语句
    sql = text("""
           DELETE FROM inverter_second_data  
           WHERE save_time BETWEEN :start_time AND :end_time   
       """)
    # 使用 session 执行查询
    with Session(db) as session:
        result = session.execute(sql, {"start_time": start_time, "end_time": end_time})
        session.commit()  # 提交事务

    # 返回删除的行数
    return result.rowcount


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# start_time =  datetime(2024, 12, 4, 17, 4, 00)
# end_time =  datetime(2024, 12, 4, 17, 5, 00)
# print(delete_inverter_second_data(db, start_time, end_time))

# 新增inverter_hourly_data元素参数数据
def write_inverter_hourly_data(db, data):
    """
    写入逆变器小时级数据
    :param db:
    :param data:
    :return:
    """
    sql = text("""  
            INSERT INTO inverter_hourly_data (  
                save_time,  
                pv_cumulative_energy,  
                information  
            ) VALUES (  
                DATE_TRUNC('hour', :save_time),  
                :pv_cumulative_energy,  
                :information  
            )  
        """)  # 将存储时间截断到小时

    # 使用 Session 执行插入
    with Session(db) as session:
        session.execute(sql, {
            "save_time": data.save_time,
            "pv_cumulative_energy": data.pv_cumulative_energy,
            "information": data.information
        })
        session.commit()  # 提交事务
    return True


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# data = InverterHourlyDataElement(save_time=datetime.now(),         # utcnow()返回全球统一时间，now()返回本时区时间
#         pv_cumulative_energy=2234.56,
#         information='示例数据2')
# write_inverter_hourly_data(db, data)

# 判断inverter_hourly_data是否有此时间点的数据
def exist_inverter_hourly_data(db, save_time=None):
    # SQL 查询语句
    sql = text("""
           SELECT 1
           FROM inverter_hourly_data
           WHERE save_time = :save_time
       """)
    # 使用 session 执行查询
    with Session(db) as session:
        result = session.execute(sql, {"save_time": save_time})
    # 判断是否有数据
    if result.fetchone():  # `fetchone` 返回第一行数据，如果有数据就为真，否则为 None
        return True
    return False


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# save_time =  datetime(2024, 12, 6, 15)
# print(exist_inverter_hourly_data(db, save_time))

# 读取inverter_hourly_data在某时间点的数据
def read_inverter_hourly_data(db, save_time=None):
    # SQL 查询语句
    sql = text("""
           SELECT *
           FROM inverter_hourly_data
           WHERE save_time = :save_time
       """)
    # 使用 session 执行查询
    with Session(db) as session:
        result = session.execute(sql, {"save_time": save_time})
    # 判断是否有数据
    rows = result.fetchall()
    return rows


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# save_time =  datetime(2024, 12, 6, 15)
# print(read_inverter_hourly_data(db, save_time))

# 删除inverter_hourly_data在某时间段的数据
def delete_inverter_hourly_data(db, start_time=None, end_time=None):
    # SQL 查询语句
    sql = text("""
           DELETE FROM inverter_hourly_data  
           WHERE save_time BETWEEN :start_time AND :end_time   
       """)
    # 使用 session 执行查询
    with Session(db) as session:
        result = session.execute(sql, {"start_time": start_time, "end_time": end_time})
        session.commit()  # 提交事务

    # 返回删除的行数
    return result.rowcount


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# start_time =  datetime(2024, 12, 4, 17, 4, 00)
# end_time =  datetime(2024, 12, 6, 17, 5, 00)
# print(delete_inverter_hourly_data(db, start_time, end_time))


# 新增para_data元素参数数据
def write_para_data(db, data):
    """
    写入para_data数据表，为仿真之中的电力相关参数

    :param db:
    :param data:
    :return:
    """
    sql = text("""
                insert into para_data (
                    year,
                    region,
                    pv_carbon_factor,
                    grid_carbon_factor,
                    electricity_price,
                    information
                )
                values(
                    :year,
                    :region,
                    :pv_carbon_factor,
                    :grid_carbon_factor,
                    :electricity_price,
                    :information
                )
        """)

    with Session(db) as session:
        session.execute(sql, {
            "year": data.year,
            "region": data.region,
            "pv_carbon_factor": data.pv_carbon_factor,
            "grid_carbon_factor": data.grid_carbon_factor,
            "electricity_price": data.electricity_price,
            "information": data.information
        })
        session.commit()
    return True


# # 读取para_data元素参数数据
def read_para_data(db, year, region):
    try:
        with Session(db) as session:
            sql = text(f"SELECT * FROM para_data WHERE year = :year_value AND region = :region_value")
            result = session.execute(sql, {"year_value": year, "region_value": region}).fetchone()
            return result
    except Exception as e:
        print(f"Error in read_para_data:{e}")
        return None


# engine = create_engine('postgresql://postgres:123456@localhost:5432/system_database', echo=True)
# db = engine.connect()
# data = ParaDataElement(
#     year=2024,
#     region="wuhan",
#     pv_carbon_factor=100,
#     grid_carbon_factor=100,
#     electricity_price=1,
#     information='元素备用信息'
# )
# # write_para_data(db,data)
# data_read = read_para_data(db,2024,"wuhan")
# print(data_read)

# 写入单个基站秒级数据
def write_second_bs_data(Session, bs_id):
    session = Session()  # 使用 Session 开启一个事务
    try:
        bs_data = bs_second_data_format_transformation(bs_id)
        # 写入数据库
        sql = text("""
            INSERT INTO public.bs_second_data (time, bs_id, cell_1_user_count, cell_2_user_count, uplink_throughput, downlink_throughput) VALUES (:time, :bs_id, :cell_1_user_count, :cell_2_user_count, :uplink_throughput, :downlink_throughput)
        """)
        session.execute(sql, {
            "time": bs_data.time,
            "bs_id": bs_data.bs_id,
            "cell_1_user_count": bs_data.cell_1_user_count,
            "cell_2_user_count": bs_data.cell_2_user_count,
            "uplink_throughput": bs_data.uplink_throughput,
            "downlink_throughput": bs_data.downlink_throughput
        })
        session.commit()
        print(f"基站秒级数据写入成功：基站编号 {bs_data.bs_id}，时间 {bs_data.time}。")
    except ValueError as ve:
        print(f"数据校验失败：{ve}")
        session.rollback()  # 出现错误时回滚
    except Exception as e:
        print(f"写入数据库时发生错误：{e}")
        session.rollback()  # 出现错误时回滚
    finally:
        session.close()  # 关闭 Session


# 写入单个基站分钟级数据
def write_bs_minute_data(Session, bs_id):
    session = Session()  # 使用 Session 开启一个事务
    try:
        bs_data = bs_minute_data_format_transformation(bs_id)
        # Step 5: 写入数据库
        sql = text(
            """INSERT INTO public.bs_minute_data (time, bs_id, transmit_power) VALUES (:time, :bs_id, :transmit_power)""")
        session.execute(sql, {
            "time": bs_data.time,
            "bs_id": bs_data.bs_id,
            "transmit_power": bs_data.transmit_power
        })
        session.commit()

        print(f"基站分钟级数据写入成功：基站编号 {bs_data.bs_id}，时间 {bs_data.time}。")
    except ValueError as ve:
        print(f"数据校验失败：{ve}")
        session.rollback()  # 出现错误时回滚
    except Exception as e:
        print(f"写入数据库时发生错误：{e}")
        session.rollback()  # 出现错误时回滚
    finally:
        session.close()  # 关闭 Session


def write_bs_hourly_data(Session, bs_id):
    session = Session()  # 使用 Session 开启一个事务
    try:
        bs_data = bs_hour_data_format_transformation(bs_id)

        # Step 5: 写入数据库
        current_time = datetime.now()
        sql = text("""
            INSERT INTO public.bs_hourly_data (
                time, bs_id, uplink_bandwidth, downlink_bandwidth,
                sleep_period, rf_status, ru_power_status, ru_status
            ) VALUES (
                :time, :bs_id, :uplink_bandwidth, :downlink_bandwidth,
                :sleep_period, :rf_status, :ru_power_status, :ru_status
            )
        """)
        params = {
            "time": current_time,
            "bs_id": bs_id,
            "uplink_bandwidth": bs_data.uplink_bandwidth,
            "downlink_bandwidth": bs_data.downlink_bandwidth,
            "sleep_period": bs_data.sleep_period,
            "rf_status": bs_data.rf_status,
            "ru_power_status": bs_data.ru_power_status,
            "ru_status": bs_data.ru_status
        }
        session.execute(sql, params)
        session.commit()

        print(f"小时级基站数据写入成功：基站 {bs_id}，时间 {current_time}。")
    except Exception as e:
        session.rollback()
        print(f"写入小时级基站数据时发生错误：{e}")
    finally:
        session.close()


def write_bs_virtual_scene(Session, bs_number):
    """
    调整虚拟基站数量，方式是删除已有的数据，重新创建virtualscene表格当中的全部数据
    初始化数据已经写死
    :param Session:
    :param bs_number: 仿真中基站的数量
    :return:
    """
    # 调整虚拟基站数量，方式是删除已有的数据，重新创建virtualscene表格当中的全部数据
    # 初始化数据已经写死
    delete_all_virtual_scene(Session)
    session = Session()
    for i in range(bs_number):
        bandwidth = 100 if i == 0 else 100
        power = 120 if i == 0 else 40
        try:
            current_time = datetime.now()
            sql = text("""
                INSERT INTO public.virtualscene(
                    time, bs_number, user_number, transmit_power, bandwidth, sleep_period, rf_status, 
                    bs_location_generation_algorithm, user_location_generation_algorithm, information
                ) VALUES (
                    :time, :bs_number, :user_number, :transmit_power, :bandwidth, :sleep_period, :rf_status,
                    :bs_location_generation_algorithm, :user_location_generation_algorithm, :information
                )
            """)
            params = {
                # "bs_identifier": bs_identifier,
                "time": current_time,
                # "bs_number": const.BS_NUM,
                "bs_number": bs_number,
                "user_number": 30,
                # "user_number": const.UE_NUM_SEQUENCE[0],

                "transmit_power": power,
                "bandwidth": bandwidth,
                # "sleep_period": "20:43:00-20:44:57",
                # 新修改的时间，与预先设定格式一致,其中以1h为一个基本时差单位
                "sleep_period": "00:00-00:00",
                "rf_status": True,

                # 目前只有"uniform2d"这一种
                # "bs_location_generation_algorithm": 'bs_sleep',
                "bs_location_generation_algorithm": "uniform2d",
                # "user_location_generation_algorithm": 'linear regression',
                "user_location_generation_algorithm": "uniform2d",
                "information": None
            }
            session.execute(sql, params)
            session.commit()
            print(f"成功初始化 {bs_number} 条场景信息。")
        except Exception as e:
            session.rollback()
            print(f"写入虚拟基站数据时发生错误：{e}")
        finally:
            session.close()

# TODO 最常用的函数之一
def initilize_bs_virtual_scene(Session, bs_number, bs_loc_alg="uniform2d", user_loc_alg="uniform2d"):
    """
    新的基站初始化方法，支持参数的传入初始设置
    初始化可以接受的参数：基站分布方式，用户分布方，接入方式，优化算法
    :param Session:
    :param bs_number: 仿真中基站的数量
    :return:
    """
    # 调整虚拟基站数量，方式是删除已有的数据，重新创建virtualscene表格当中的全部数据
    # 初始化数据已经写死
    delete_all_virtual_scene(Session)
    session = Session()
    for _ in range(bs_number):
        try:
            current_time = datetime.now()
            sql = text("""
                INSERT INTO public.virtualscene(
                    time, bs_number, user_number, transmit_power, bandwidth, sleep_period, rf_status, 
                    bs_location_generation_algorithm, user_location_generation_algorithm, information
                ) VALUES (
                    :time, :bs_number, :user_number, :transmit_power, :bandwidth, :sleep_period, :rf_status,
                    :bs_location_generation_algorithm, :user_location_generation_algorithm, :information
                )
            """)
            params = {
                "time": current_time,
                "bs_number": bs_number,

                # 实际上user数量不在这里体现
                "user_number": 30,
                # "user_number": const.UE_NUM_SEQUENCE[0],

                "transmit_power": 20,
                "bandwidth": 100,
                "sleep_period": "00:00:00-00:10:00",
                "rf_status": True,

                "bs_location_generation_algorithm": bs_loc_alg,
                "user_location_generation_algorithm": user_loc_alg,
                "information": None
            }
            session.execute(sql, params)
            session.commit()
            print(f"成功初始化 {bs_number} 条场景信息。")
        except Exception as e:
            session.rollback()
            print(f"写入虚拟基站数据时发生错误：{e}")
        finally:
            session.close()


def get_bs_virtual_scene(Session, bs_identifier, paraname):
    """
    获取拟场景基站的参数值
    :param Session:
    :param bs_identifier: 读取虚拟场景基站的id
    :param paraname: 读取参数的名称
    :return:直接返回获取参数的值
    """
    session = Session()
    try:
        sql = text(f"""
            SELECT {paraname} FROM public.virtualscene
            WHERE bs_identifier = :bs_identifier_value
        """)
        result = session.execute(sql, {
            "bs_identifier_value": bs_identifier
        }).fetchone()[0]
        print(f"成功读取虚拟场景表中第{bs_identifier}基站固定信息数据的{paraname}:{result}")
        return result

    except Exception as e:
        session.rollback()
        print(f"读取时遇到错误: {e}")
        return False
    finally:
        session.close()

# TODO显示打印信息
def update_bs_virtual_scene(Session, bs_identifier, updated_param, updated_data, showInfo=False):
    """
    仿真中对于特定序号的基站进行参数的更新，最常用的一个函数
    可以更新bs_number, user_number, transmit_power, bandwidth, sleep_period, rf_status, bs_location_generation_algorithm, user_location_generation_algorithm, information
    :param Session: 会话
    :param bs_identifier: 需要更新的基站号码
    :param updated_param: 需要更新的参数名
    :param updated_data: 需要更新的参数值
    :return:
    """
    session = Session()
    try:
        if not isinstance(updated_param, str):
            raise ValueError(f"Invalid column name: {updated_param}")

        if updated_param == 'bs_location_generation_algorithm' or updated_param == 'user_location_generation_algorithm':
            update_sql = text(f"""
                                           UPDATE public.virtualscene
                                           SET {updated_param} = :updated_data
                                       """)
            params = {
                "updated_data": updated_data
            }
        else:
            update_sql = text(f"""
                                   UPDATE public.virtualscene
                                   SET {updated_param} = :updated_data
                                   WHERE bs_identifier = :bs_identifier
                               """)
            params = {
                "bs_identifier": bs_identifier,
                "updated_data": updated_data
            }
        session.execute(update_sql, params)
        if showInfo:
            print(f"Updated {updated_param} for bs_identifier {bs_identifier} to {updated_data}.")
        session.commit()
    except Exception as e:
        # 回滚事务
        session.rollback()
        print(f"An error occurred while updating {updated_param}: {e}")
    finally:
        session.close()


def update_all_bs_virtual_scene(Session, bs_num, updated_param, updated_data):
    """
    仿真中对于所有基站进行参数的更新
    :param Session:
    :param bs_num: 基站的数量
    :param updated_param:
    :param updated_data:
    :return:
    """
    try:
        for bs_id in range(1, bs_num + 1):
            update_bs_virtual_scene(Session, bs_id, updated_param, updated_data)
        return True
    except Exception as e:
        print(f"An error occurred while updating {updated_param}: {e}")
        return False


def read_bs_virtual_scene(Session, parameter_name):
    """

    :param Session:
    :param parameter_name: 需要查找的参数名
    :return: 如果成功，那么则直接返回参数
    """
    # print(parameter_name)
    session = Session()
    try:
        sql = text(f"""
                SELECT {parameter_name} FROM public.virtualscene
                ORDER BY time DESC
                LIMIT 1
            """)
        result = session.execute(sql).fetchone()
        if result:
            return result[0]
        else:
            return f"没有找到数据！"
    except Exception as e:
        print(f"读取虚拟基站数据时发生错误：{e}")
    finally:
        session.close()


def read_specific_id_bs_virtual_scene(Session, bs_identifier, parameter_name="sleep_period"):
    """
    查询某一个基站的特定数据，一位内基站睡眠时间需要，所以进行默认
    :param Session:
    :param bs_identifier:需要查找基站信息的id
    :param parameter_name: 需要查找的参数名, 默认为"sleep_period"
    :return: 如果成功，那么则直接返回参数
    """
    # print(parameter_name)
    session = Session()
    sql = text(f"""
            SELECT {parameter_name} FROM public.virtualscene
            WHERE  bs_identifier = {bs_identifier}
        """)
    try:
        result = session.execute(sql).fetchone()
        if result:
            return result[0]
        else:
            return f"没有找到数据！"
    except Exception as e:
        print(f"读取虚拟基站数据时发生错误：{e}")
    finally:
        session.close()


def delete_all_virtual_scene(Session):
    """
    删除所有的virtualscene数据表
    :param Session: 当前的会话
    :return:
    """
    session = Session()
    try:
        # 编写删除所有数据的 SQL
        sql = text("""
                DELETE FROM public.virtualscene
            """)
        session.execute(sql)
        reset_sequence_sql = text("""
                    ALTER SEQUENCE public.virtualscene_bs_identifier_seq RESTART WITH 1
                """)
        session.execute(reset_sequence_sql)
        # 执行删除操作

        # 提交事务
        session.commit()
        print(f"Deleted all records and reset bs_identifier sequence to start from 1.")
    except Exception as e:
        # 如果出现异常，回滚事务
        session.rollback()
        print(f"An error occurred while deleting all records: {e}")
    finally:
        session.close()


def read_bs_hourly_data(session, bs_id, parameter_name):
    """
    从数据库读取基站小时级数据。

    参数：
        session: 数据库会话对象。
        bs_id: 基站ID。
        parameter_name: 需要查询的字段名。
    """
    valid_columns = [
        "time", "bs_id", "uplink_bandwidth", "downlink_bandwidth",
        "sleep_period", "rf_status", "ru_power_status", "ru_status"
    ]
    if parameter_name not in valid_columns:
        return f"错误：参数 '{parameter_name}' 不存在于表中！"

    sql = text(f"""
        SELECT {parameter_name} FROM public.bs_hourly_data
        WHERE bs_id = :bs_id
        ORDER BY time DESC
        LIMIT 1
    """)
    try:
        result = session.execute(sql, {"bs_id": bs_id}).fetchone()
        if result:
            return result[0]
        else:
            return f"没有找到基站 {bs_id} 的 {parameter_name} 数据！"
    except Exception as e:
        print(f"读取小时级基站数据时发生错误：{e}")
    finally:
        session.close()


# 读出秒级数据
def read_bs_second_data(bs_id, parameter_name, engine):
    # 确保参数名称存在于表中
    valid_columns = ["time", "bs_id", "cell_1_user_count", "cell_2_user_count", "uplink_throughput",
                     "downlink_throughput"]
    if parameter_name not in valid_columns:
        return f"错误：参数 '{parameter_name}' 不存在于表中！"
    # 构建查询语句
    bs_id = str(bs_id)
    sql = text(f"""
            SELECT {parameter_name}
            FROM public.bs_second_data
            WHERE bs_id = :bs_id
            ORDER BY time DESC
            LIMIT 1
        """)
    # 执行查询
    with engine.connect() as connection:
        result = connection.execute(sql, {"bs_id": bs_id}).fetchone()
        if result:
            return result[0]  # 返回查询到的值
        else:
            return f"没有找到基站 {bs_id} 对应的 {parameter_name} 数据！"


# 从数据库中读出基站分钟级数据
def read_bs_minute_data(bs_id, parameter_name, engine):
    # 确保参数名称存在于表中
    valid_columns = ["time", "bs_id", "transmit_power"]
    if parameter_name not in valid_columns:
        return f"错误：参数 '{parameter_name}' 不存在于表中！"
    # 构建查询语句
    bs_id = str(bs_id)
    sql = text(
        f"""SELECT {parameter_name} FROM public.bs_minute_data WHERE bs_id = :bs_id ORDER BY time DESC LIMIT 1 """)
    # 执行查询
    with engine.connect() as connection:
        result = connection.execute(sql, {"bs_id": bs_id}).fetchone()
        if result:
            return result[0]  # 返回查询到的值
        else:
            return f"没有找到基站 {bs_id} 对应的 {parameter_name} 数据！"


def write_inverter_second_data1(Session, bs_id):
    session = Session()
    try:
        inverter_second_data = inverter_second_data_format_transformation(bs_id)
        sql = text("""  
            INSERT INTO inverter_second_data (

                save_time,   
                inverter_active_power,   
                inverter_output_current,   
                inverter_output_voltage,   
                inverter_output_freq,   
                pv_active_power,   
                pv_output_current,   
                pv_output_voltage,   
                storage_active_power,   
                storage_output_current,   
                storage_output_voltage,   
                storage_soc,   
                information  
            ) VALUES (  

                :save_time,   
                :inverter_active_power,   
                :inverter_output_current,   
                :inverter_output_voltage,   
                :inverter_output_freq,   
                :pv_active_power,   
                :pv_output_current,   
                :pv_output_voltage,   
                :storage_active_power,   
                :storage_output_current,   
                :storage_output_voltage,   
                :storage_soc,   
                :information  
            )  
        """)
        session.execute(sql, {
            # "id": inverter_second_data.id,
            "save_time": inverter_second_data.save_time,
            "inverter_active_power": inverter_second_data.inverter_active_power,
            "inverter_output_current": inverter_second_data.inverter_output_current,
            "inverter_output_voltage": inverter_second_data.inverter_output_voltage,
            "inverter_output_freq": inverter_second_data.inverter_output_freq,
            "pv_active_power": inverter_second_data.pv_active_power,
            "pv_output_current": inverter_second_data.pv_output_current,
            "pv_output_voltage": inverter_second_data.pv_output_voltage,
            "storage_active_power": inverter_second_data.storage_active_power,
            "storage_output_current": inverter_second_data.storage_output_current,
            "storage_output_voltage": inverter_second_data.storage_output_voltage,
            "storage_soc": inverter_second_data.storage_soc,
            "information": inverter_second_data.information
        })
        session.commit()  # 提交事务
        print(f"基站秒级数据写入成功：基站编号 {inverter_second_data.id}，时间 {inverter_second_data.save_time}。")
    except ValueError as ve:
        print(f"数据校验失败：{ve}")
        session.rollback()  # 出现错误时回滚
    except Exception as e:
        print(f"写入数据库时发生错误：{e}")
        session.rollback()  # 出现错误时回滚
    finally:
        session.close()  # 关闭 Session


def write_inverter_hourly_data1(Session, bs_id):
    session = Session()
    try:
        inverter_hourly_data = inverter_hourly_data_format_transformation(bs_id)
        sql = text("""
            INSERT INTO inverter_hourly_data (

                save_time,  
                pv_cumulative_energy,  
                information  
            ) VALUES (

                DATE_TRUNC('hour', :save_time),  
                :pv_cumulative_energy,  
                :information  
            )  
        """)
        session.execute(sql, {
            # "id": inverter_hourly_data.id,
            "save_time": inverter_hourly_data.save_time,
            "pv_cumulative_energy": inverter_hourly_data.pv_cumulative_energy,
            "information": inverter_hourly_data.information
        })
        session.commit()  # 提交事务
        print(f"基站秒级数据写入成功：基站编号 {inverter_hourly_data.id}，时间 {inverter_hourly_data.save_time}。")
    except ValueError as ve:
        print(f"数据校验失败：{ve}")
        session.rollback()  # 出现错误时回滚
    except Exception as e:
        print(f"写入数据库时发生错误：{e}")
        session.rollback()  # 出现错误时回滚
    finally:
        session.close()  # 关闭 Session


def input_inverter_data(data):
    get_or_input_data("input_data", data)
